﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for PhukienController
/// </summary>
public class PhukienController:Interface
{
    private SqlConnection con;
	public PhukienController()
	{
	    con = ConnectDB.Connect();
        ConnectDB.Open();
	}

    public int Insert(object obj)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert_Phukien";
            cmd.CommandType = CommandType.StoredProcedure;
            Phukien p = (Phukien)obj;
            cmd.Parameters.Add("@danhmuc_phukien_id", SqlDbType.Int).Value = p.DanhmucPhukien_id;
            cmd.Parameters.Add("@name", SqlDbType.NText).Value = p.Name;
            cmd.Parameters.Add("@url", SqlDbType.NText).Value = p.Url;
            cmd.Parameters.Add("@description", SqlDbType.NText).Value = p.Description;
            cmd.Parameters.Add("@price_input", SqlDbType.Float).Value = p.Price_input;
            cmd.Parameters.Add("@price_output", SqlDbType.Float).Value = p.Price_output;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = p.Status;
            cmd.Parameters.Add("@image", SqlDbType.NText).Value = p.Image;
            cmd.Parameters.Add("@dateStart", SqlDbType.DateTime).Value = p.DateStart;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }
    public int InsertScalar(object obj)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert_Phukien";
            cmd.CommandType = CommandType.StoredProcedure;
            Phukien p = (Phukien)obj;
            cmd.Parameters.Add("@danhmuc_phukien_id", SqlDbType.Int).Value = p.DanhmucPhukien_id;
            cmd.Parameters.Add("@name", SqlDbType.NText).Value = p.Name;
            cmd.Parameters.Add("@url", SqlDbType.NText).Value = p.Url;
            cmd.Parameters.Add("@description", SqlDbType.NText).Value = p.Description;
            cmd.Parameters.Add("@price_input", SqlDbType.Float).Value = p.Price_input;
            cmd.Parameters.Add("@price_output", SqlDbType.Float).Value = p.Price_output;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = p.Status;
            cmd.Parameters.Add("@image", SqlDbType.NText).Value = p.Image;
            cmd.Parameters.Add("@dateStart", SqlDbType.DateTime).Value = p.DateStart;
            object result = cmd.ExecuteScalar();
            if(result==System.DBNull.Value)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(result);
            }

        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }
    public int InsertImage(int id_phukien,string image)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "INSERT INTO Phukien_Image(phukien_id,[image]) VALUES(@phukien_id,@image)";
            cmd.CommandType = CommandType.Text;
            
            cmd.Parameters.Add("@phukien_id", SqlDbType.Int).Value = id_phukien;
            cmd.Parameters.Add("@image", SqlDbType.NText).Value = image;
            return cmd.ExecuteNonQuery();

        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public int Update(object obj)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Update_Phukien";
            cmd.CommandType = CommandType.StoredProcedure;
            Phukien p = (Phukien)obj;
            cmd.Parameters.Add("@phukien_id", SqlDbType.Int).Value = p.Phukien_id;
            cmd.Parameters.Add("@danhmuc_phukien_id", SqlDbType.Int).Value = p.DanhmucPhukien_id;
            cmd.Parameters.Add("@name", SqlDbType.NText).Value = p.Name;
            cmd.Parameters.Add("@url", SqlDbType.NText).Value = p.Url;
            cmd.Parameters.Add("@description", SqlDbType.NText).Value = p.Description;
            cmd.Parameters.Add("@price_input", SqlDbType.Float).Value = p.Price_input;
            cmd.Parameters.Add("@price_output", SqlDbType.Float).Value = p.Price_output;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = p.Status;
            cmd.Parameters.Add("@image", SqlDbType.NText).Value = p.Image;
            cmd.Parameters.Add("@dateUpdate", SqlDbType.DateTime).Value = p.DateUpdate;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public int Delete(int id)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Delete_Phukien";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@phukien_id", SqlDbType.Int).Value = id;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public DataTable GetAll()
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_Phukien ORDER BY dateStart DESC";
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }

    public DataTable GetAll1()
<<<<<<< .mine=======    //    DataTable dt = new DataTable();
    //    try
    //    {
    //        SqlCommand cmd = new SqlCommand();
    //        cmd.CommandText = "SELECT dbo.Product.product_id, dbo.Product.model, dbo.Product.price_output, dbo.Product_Image.[image] FROM dbo.Product INNER JOIN dbo.Product_Image ON dbo.Product.product_id = dbo.Product_Image.product_id";
    //        cmd.CommandType = CommandType.Text;
    //        SqlDataAdapter da = new SqlDataAdapter();
    //        cmd.Connection = con;
    //        da.SelectCommand = cmd;
    //        da.Fill(dt);
    //        return dt;
    //    }
    //    catch (Exception)
    //    {

    //        return null;
    //        throw;
    //    }
    //}

    public DataTable GetByID(int id)
>>>>>>> .theirs    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_Phukien WHERE phukien_id=@id ORDER BY dateStart DESC";
            cmd.Parameters.AddWithValue("@id", id);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }
    public DataTable GetByProductID(int product_id)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT pk.* FROM Phukien AS pk INNER JOIN Product_Phukien AS pp ON pk.phukien_id=pp.phukien_id INNER JOIN Product AS p ON pp.product_id=p.product_id WHERE p.product_id=@product_id ORDER BY dateStart DESC";
            cmd.Parameters.AddWithValue("@product_id", product_id);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }

    
    public List<Phukien_Image> getImagePhukien(int phukien_id)
    {
        List<Phukien_Image> list = new List<Phukien_Image>();
        try
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            
            cmd.CommandText = "Select_Phukien_Image_By_ID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@phukien_id", SqlDbType.Int).Value = phukien_id;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            cmd.Connection = con;
            da.Fill(dt);
            foreach (DataRow item in dt.Rows)
            {
                Phukien_Image pki = new Phukien_Image(item);
                list.Add(pki);
            }
            return list;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }
    public int deleteImagePhukien(int phukien_image_id)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "DELETE FROM Phukien_Image WHERE phukien_image_id=@phukien_image_id";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@phukien_image_id", SqlDbType.Int).Value = phukien_image_id;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }
}